import xlrd
import xlwt
from xlutils.copy import copy
from xlwt import XFStyle

import DateTime
import Mongo

qxname = DateTime.DateTime().qxname()

qx = Mongo.Mongo("qx", qxname)  # 读取当月抢修表
count = qx.collCount()  # 读取当月抢修表的个数
jsb = Mongo.Mongo("JSB", "jsb")  # 当去结算表


def yyyy():
    style = XFStyle()  # 赋值style为XFStyle()，初始化样式
    styletop = XFStyle()  # 赋值style为XFStyle()，初始化样式

    font = xlwt.Font()
    font.name = "宋体"
    font.bold = False  # 是否加粗
    font.height = 20 * 9
    style.font = font

    font1 = xlwt.Font()
    font1.name = "宋体"
    font1.bold = True  # 是否加粗
    font1.height = 20 * 13
    styletop.font = font1

    borders = xlwt.Borders()
    borders.top = 1
    borders.bottom = 1
    borders.left = 1
    borders.right = 1
    style.borders = borders

    borders1 = xlwt.Borders()
    borders1.top = 1
    borders1.bottom = 1
    borders1.left = 1
    borders1.right = 0
    styletop.borders = borders1

    alignment = xlwt.Alignment()
    alignment.horz = 0x02
    alignment.vert = 0x01
    alignment.wrap = 1
    style.alignment = alignment

    alignment1 = xlwt.Alignment()
    alignment1.horz = 0x02
    alignment1.vert = 0x01
    alignment1.wrap = 0
    styletop.alignment = alignment1
    year = DateTime.DateTime().MkdirYear()
    month = DateTime.DateTime().MkdirMonth()
    foldname = "D:/" + year + "/" + month + "/总表"  # 文件夹盘所在D盘的位置)
    rb = xlrd.open_workbook('./excel/抢修确认签证单moban.xls', formatting_info=True)  # 打开weng.xls文件
    wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
    ws = wb.get_sheet(0)  # 打开材料表
    wsSB = wb.get_sheet(1)
    wsCL = wb.get_sheet(2)
    wsHM = wb.get_sheet(3)
    wsWGQ = wb.get_sheet(4)
    wsYWB = wb.get_sheet(5)
    SB=[]
    CL=[]
    HM=[]
    WGQ=[]
    YWB=[]
    num=0
    for nQX in range(1, count + 1):  # 遍历所有抢修表
        if qx.finddict({"_id": nQX})["pianqu"] == "世博":
            num+=1
            SB.append(qx.finddict({"_id": nQX})["_id"])
        elif qx.finddict({"_id": nQX})["pianqu"] == "曹路":
            num += 1
            CL.append(qx.finddict({"_id": nQX})["_id"])
        elif qx.finddict({"_id": nQX})["pianqu"] == "花木":
            num += 1
            HM.append(qx.finddict({"_id": nQX})["_id"])
        elif qx.finddict({"_id": nQX})["pianqu"] == "外高桥":
            num += 1
            WGQ.append(qx.finddict({"_id": nQX})["_id"])
        elif qx.finddict({"_id": nQX})["pianqu"] == "运维部":
            num += 1
            YWB.append(qx.finddict({"_id": nQX})["_id"])
        else:print("有遗漏项,请核查各片区抢修数量")

    lSB=len(SB)
    jiageSB=0
    jiageSBTB=0
    for n in range(0,lSB):
        jiageSB+=qx.finddict({"_id":SB[n]})["jiage"]
        jiageSBTB+=qx.finddict({"_id": SB[n]})["TBjiage"]
        ws.write(4+n, 1, qx.finddict({"_id":SB[n]})["code"], style)
        ws.write(4+n, 2, qx.finddict({"_id":SB[n]})["dizhi"]+qx.finddict({"_id":SB[n]})["leixing"], style)
        ws.write(4+n, 3, qx.finddict({"_id":SB[n]})["dizhi"], style)
        ws.write(4+n, 4, qx.finddict({"_id":SB[n]})["shijian"], style)
        ws.write(4+n, 5, qx.finddict({"_id":SB[n]})["jiage"], style)
        ws.write(4+n, 6, qx.finddict({"_id":SB[n]})["TBjiage"], style)
        ws.write(4+n, 7, qx.finddict({"_id":SB[n]})["TaiBan"], style)

        wsSB.write(4+n, 1, qx.finddict({"_id":SB[n]})["code"], style)
        wsSB.write(4+n, 2, qx.finddict({"_id":SB[n]})["dizhi"]+qx.finddict({"_id":SB[n]})["leixing"], style)
        wsSB.write(4+n, 3, qx.finddict({"_id":SB[n]})["dizhi"], style)
        wsSB.write(4+n, 4, qx.finddict({"_id":SB[n]})["shijian"], style)
        wsSB.write(4+n, 5, qx.finddict({"_id":SB[n]})["jiage"], style)
        wsSB.write(4+n, 6, qx.finddict({"_id":SB[n]})["TBjiage"], style)
        wsSB.write(4+n, 7, qx.finddict({"_id":SB[n]})["TaiBan"], style)


    lCL=len(CL)
    jiageCL = 0
    jiageCLTB = 0
    for nn in range(0, lCL):
        jiageCL += qx.finddict({"_id": CL[nn]})["jiage"]
        jiageCLTB += qx.finddict({"_id": CL[nn]})["TBjiage"]
        ws.write(37 + nn, 1, qx.finddict({"_id": CL[nn]})["code"], style)
        ws.write(37 + nn, 2, qx.finddict({"_id": CL[nn]})["dizhi"] + qx.finddict({"_id": CL[nn]})["leixing"], style)
        ws.write(37 + nn, 3, qx.finddict({"_id": CL[nn]})["dizhi"], style)
        ws.write(37 + nn, 4, qx.finddict({"_id": CL[nn]})["shijian"], style)
        ws.write(37 + nn, 5, qx.finddict({"_id": CL[nn]})["jiage"], style)
        ws.write(37 + nn, 6, qx.finddict({"_id": CL[nn]})["TBjiage"], style)
        ws.write(37 + nn, 7, qx.finddict({"_id": CL[nn]})["TaiBan"], style)

        wsCL.write(4 + nn, 1, qx.finddict({"_id": CL[nn]})["code"], style)
        wsCL.write(4 + nn, 2, qx.finddict({"_id": CL[nn]})["dizhi"] + qx.finddict({"_id": CL[nn]})["leixing"], style)
        wsCL.write(4 + nn, 3, qx.finddict({"_id": CL[nn]})["dizhi"], style)
        wsCL.write(4 + nn, 4, qx.finddict({"_id": CL[nn]})["shijian"], style)
        wsCL.write(4 + nn, 5, qx.finddict({"_id": CL[nn]})["jiage"], style)
        wsCL.write(4 + nn, 6, qx.finddict({"_id": CL[nn]})["TBjiage"], style)
        wsCL.write(4 + nn, 7, qx.finddict({"_id": CL[nn]})["TaiBan"], style)
    lHM=len(HM)
    jiageHM = 0
    jiageHMTB = 0
    for nnn in range(0, lHM):
        jiageHM += qx.finddict({"_id": HM[nnn]})["jiage"]
        jiageHMTB += qx.finddict({"_id": HM[nnn]})["TBjiage"]
        ws.write(60 + nnn, 1, qx.finddict({"_id": HM[nnn]})["code"], style)
        ws.write(60 + nnn, 2, qx.finddict({"_id": HM[nnn]})["dizhi"] + qx.finddict({"_id": HM[nnn]})["leixing"], style)
        ws.write(60 + nnn, 3, qx.finddict({"_id": HM[nnn]})["dizhi"], style)
        ws.write(60 + nnn, 4, qx.finddict({"_id": HM[nnn]})["shijian"], style)
        ws.write(60 + nnn, 5, qx.finddict({"_id": HM[nnn]})["jiage"], style)
        ws.write(60 + nnn, 6, qx.finddict({"_id": HM[nnn]})["TBjiage"], style)
        ws.write(60 + nnn, 7, qx.finddict({"_id": HM[nnn]})["TaiBan"], style)

        wsHM.write(4 + nnn, 1, qx.finddict({"_id": HM[nnn]})["code"], style)
        wsHM.write(4 + nnn, 2, qx.finddict({"_id": HM[nnn]})["dizhi"] + qx.finddict({"_id": HM[nnn]})["leixing"], style)
        wsHM.write(4 + nnn, 3, qx.finddict({"_id": HM[nnn]})["dizhi"], style)
        wsHM.write(4 + nnn, 4, qx.finddict({"_id": HM[nnn]})["shijian"], style)
        wsHM.write(4 + nnn, 5, qx.finddict({"_id": HM[nnn]})["jiage"], style)
        wsHM.write(4 + nnn, 6, qx.finddict({"_id": HM[nnn]})["TBjiage"], style)
        wsHM.write(4 + nnn, 7, qx.finddict({"_id": HM[nnn]})["TaiBan"], style)
    lWGQ=len(WGQ)
    jiageWGQ = 0
    jiageWGQTB = 0
    for nnnn in range(0, lWGQ):
        jiageWGQ += qx.finddict({"_id": WGQ[nnnn]})["jiage"]
        jiageWGQTB += qx.finddict({"_id": WGQ[nnnn]})["TBjiage"]
        ws.write(83 + nnnn, 1, qx.finddict({"_id": WGQ[nnnn]})["code"], style)
        ws.write(83 + nnnn, 2, qx.finddict({"_id": WGQ[nnnn]})["dizhi"] + qx.finddict({"_id": WGQ[nnnn]})["leixing"], style)
        ws.write(83 + nnnn, 3, qx.finddict({"_id": WGQ[nnnn]})["dizhi"], style)
        ws.write(83 + nnnn, 4, qx.finddict({"_id": WGQ[nnnn]})["shijian"], style)
        ws.write(83 + nnnn, 5, qx.finddict({"_id": WGQ[nnnn]})["jiage"], style)
        ws.write(83 + nnnn, 6, qx.finddict({"_id": WGQ[nnnn]})["TBjiage"], style)
        ws.write(83 + nnnn, 7, qx.finddict({"_id": WGQ[nnnn]})["TaiBan"], style)

        wsWGQ.write(4 + nnnn, 1, qx.finddict({"_id": WGQ[nnnn]})["code"], style)
        wsWGQ.write(4 + nnnn, 2, qx.finddict({"_id": WGQ[nnnn]})["dizhi"] + qx.finddict({"_id": WGQ[nnnn]})["leixing"], style)
        wsWGQ.write(4 + nnnn, 3, qx.finddict({"_id": WGQ[nnnn]})["dizhi"], style)
        wsWGQ.write(4 + nnnn, 4, qx.finddict({"_id": WGQ[nnnn]})["shijian"], style)
        wsWGQ.write(4 + nnnn, 5, qx.finddict({"_id": WGQ[nnnn]})["jiage"], style)
        wsWGQ.write(4 + nnnn, 6, qx.finddict({"_id": WGQ[nnnn]})["TBjiage"], style)
        wsWGQ.write(4 + nnnn, 7, qx.finddict({"_id": WGQ[nnnn]})["TaiBan"], style)
    lYWB=len(YWB)
    jiageYWB = 0
    jiageYWBTB = 0
    for nnnnn in range(0, lYWB):
        jiageYWB += qx.finddict({"_id": YWB[nnnnn]})["jiage"]
        jiageYWBTB += qx.finddict({"_id": YWB[nnnnn]})["TBjiage"]
        ws.write(106 + nnnnn, 1, qx.finddict({"_id": YWB[nnnnn]})["code"], style)
        ws.write(106 + nnnnn, 2, qx.finddict({"_id": YWB[nnnnn]})["dizhi"] + qx.finddict({"_id": YWB[nnnnn]})["leixing"], style)
        ws.write(106 + nnnnn, 3, qx.finddict({"_id": YWB[nnnnn]})["dizhi"], style)
        ws.write(106 + nnnnn, 4, qx.finddict({"_id": YWB[nnnnn]})["shijian"], style)
        ws.write(106 + nnnnn, 5, qx.finddict({"_id": YWB[nnnnn]})["jiage"], style)
        ws.write(106 + nnnnn, 6, qx.finddict({"_id": YWB[nnnnn]})["TBjiage"], style)
        ws.write(106 + nnnnn, 7, qx.finddict({"_id": YWB[nnnnn]})["TaiBan"], style)

        wsYWB.write(4 + nnnnn, 1, qx.finddict({"_id": YWB[nnnnn]})["code"], style)
        wsYWB.write(4 + nnnnn, 2, qx.finddict({"_id": YWB[nnnnn]})["dizhi"] + qx.finddict({"_id": YWB[nnnnn]})["leixing"], style)
        wsYWB.write(4 + nnnnn, 3, qx.finddict({"_id": YWB[nnnnn]})["dizhi"], style)
        wsYWB.write(4 + nnnnn, 4, qx.finddict({"_id": YWB[nnnnn]})["shijian"], style)
        wsYWB.write(4 + nnnnn, 5, qx.finddict({"_id": YWB[nnnnn]})["jiage"], style)
        wsYWB.write(4 + nnnnn, 6, qx.finddict({"_id": YWB[nnnnn]})["TBjiage"], style)
        wsYWB.write(4 + nnnnn, 7, qx.finddict({"_id": YWB[nnnnn]})["TaiBan"], style)




    print("抢修确认单共"+str(num)+"项")
    ws.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)
    wsSB.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)
    wsCL.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)
    wsHM.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)
    wsWGQ.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)
    wsYWB.write(0, 0, "上海浦东东方有线网络有限公司"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认表", styletop)

    ws.write(34, 0,"世博合计金额:"+str(round(jiageSB,2))+"元，实际金额:"+str(round(jiageSBTB,2))+"元", styletop)
    wsSB.write(34, 0,"世博合计金额:"+str(round(jiageSB,2))+"元，实际金额:"+str(round(jiageSBTB,2))+"元", styletop)

    ws.write(57, 0,"曹路合计金额:"+str(round(jiageCL,2))+"元，实际金额:"+str(round(jiageCLTB,2))+"元", styletop)
    wsCL.write(24, 0,"曹路合计金额:"+str(round(jiageCL,2))+"元，实际金额:"+str(round(jiageCLTB,2))+"元", styletop)

    ws.write(80, 0,"花木合计金额:"+str(round(jiageHM,2))+"元，实际金额:"+str(round(jiageHMTB,2))+"元", styletop)
    wsHM.write(24, 0,"花木合计金额:"+str(round(jiageHM,2))+"元，实际金额:"+str(round(jiageHMTB,2))+"元", styletop)

    ws.write(103, 0,"外高桥合计金额:"+str(round(jiageWGQ,2))+"元，实际金额:"+str(round(jiageWGQTB,2))+"元", styletop)
    wsWGQ.write(24, 0,"外高桥合计金额:"+str(round(jiageWGQ,2))+"元，实际金额:"+str(round(jiageWGQTB,2))+"元", styletop)

    ws.write(126, 0,"运维部合计金额:"+str(round(jiageYWB,2))+"元，实际金额:"+str(round(jiageYWBTB,2))+"元", styletop)
    wsYWB.write(24, 0,"运维部合计金额:"+str(round(jiageYWB,2))+"元，实际金额:"+str(round(jiageYWBTB,2))+"元", styletop)

    wb.save(foldname + "/"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修确认单.xls")

